/*
 * LoginBroker.java
 *
 * Created on 13 de agosto de 2008, 11:24 AM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package flexbrokers;

import flex.messaging.io.ArrayCollection;
import flex.messaging.io.amf.ASObject;
import helpers.DateHelper;
import java.lang.reflect.InvocationTargetException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import model.Action;
import model.Sistema;
import org.apache.commons.collections.IterableMap;
import org.apache.commons.collections.MapIterator;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.beanutils.BeanUtils;
import listeners.InitApp;
import model.Salesman;

/**
 *
 * @author Alirio
 */
public class LoginBroker {
  
  private final String SQL_UPDATE_1 = "update salesmen set name=?, region=?, top_serial=?, bottom_serial=?, comments=?, last_serial=?, movil=? ,mail=?,mail_coordinador=? " +
      "where salesman_id=?";
  private final String SQL_DELETE_1 = "delete from salesmen_sub where salesman_id=?";
  private final String SQL_DELETE_2 = "delete from salesmen_actions where salesman_id=?";
  private final String SQL_INSERT_1 = "insert into salesmen_sub (salesman_id,salesman_id_sub) " +
      "values(?,?)";
  private final String SQL_INSERT_3 = "insert into salesmen_actions (salesman_id,action_id) " +
      "values(?,?)";
  private final String SQL_INSERT_2 = "insert into salesmen (salesman_id,name,region,top_serial,bottom_serial,comments,last_serial,movil,mail,mail_coordinador) " +
      "values(?,?,?,?,?,?,?,?,?,?)";
  
   private final String SQL_UPDATE_2 = "update system set estado=?, motivo=?, usuario=?, fecha=?, hora=? ";
  
  /** Creates a new instance of LoginBroker */
  public LoginBroker() {
  }
  
  public Salesman checkLogin(String uid, String pwd) {
    java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
    java.sql.Statement stmt;
    Salesman salesman = null;
    try {
      stmt = cnn.createStatement();
      java.sql.ResultSet rs = stmt.executeQuery("select * from salesmen where salesman_id='"+uid+"'");
      while (rs.next()) {
        if (rs.getString("comments").equals(pwd)) {
          if (rs.getByte("movil") == 0) {
           
            salesman = new Salesman();
            salesman.setId(uid);
            salesman.setName(rs.getString("name"));
            salesman.setTopSerial(rs.getInt("top_serial"));
            salesman.setBottomSerial(rs.getInt("bottom_serial"));
            
            java.sql.Statement stmt3 = cnn.createStatement();
            java.sql.ResultSet rs3 = stmt3.executeQuery("select salesmen_actions.action_id, actions.name from salesmen_actions, actions where salesmen_actions.action_id=actions.action_id and salesmen_actions.salesman_id='" + uid + "'");
            Action a = null;
            while (rs3.next()) {
              a = new Action();
              a.setId(rs3.getInt("action_id"));
              a.setName(rs3.getString("name"));
              salesman.addAction(a);
            }
            rs3.close();
            stmt3.close();

          }
        }
      }
      rs.close();
      stmt.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    return salesman;
  }
  
  
  public Salesman getSalesman(String uid) {
    java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
    java.sql.Statement stmt;
    Salesman salesman = null;
    try {
      stmt = cnn.createStatement();
      java.sql.ResultSet rs = stmt.executeQuery("select * from salesmen where salesman_id='"+uid+"'");
      while (rs.next()) {
        salesman = new Salesman();
        salesman.setId(uid);
        salesman.setName(rs.getString("name"));
        salesman.setTopSerial(rs.getInt("top_serial"));
        salesman.setBottomSerial(rs.getInt("bottom_serial"));
        salesman.setRegion(rs.getString("region"));
        salesman.setMovil(rs.getByte("movil"));
        salesman.setPassword(rs.getString("comments"));
        salesman.setLastSerial(rs.getInt("last_serial"));
        salesman.setMail(rs.getString("mail"));
        salesman.setMailCoordinador(rs.getString("mail_coordinador"));
        
        java.sql.Statement stmt2 = cnn.createStatement();
        java.sql.ResultSet rs2 = stmt2.executeQuery("select salesmen_sub.salesman_id_sub, salesmen.name from salesmen_sub, salesmen where salesmen.salesman_id = salesmen_sub.salesman_id_sub and salesmen_sub.salesman_id='" + uid + "'");
        
        Salesman s = null;
        while (rs2.next()) {
          s = new Salesman(rs2.getString("salesman_id_sub"), rs2.getString("name"));
          salesman.addSubordinate(s);
        }
        
        rs2.close();
        stmt2.close();
        
        java.sql.Statement stmt3 = cnn.createStatement();
        java.sql.ResultSet rs3 = stmt3.executeQuery("select salesmen_actions.action_id, actions.name from salesmen_actions, actions where salesmen_actions.action_id=actions.action_id and salesmen_actions.salesman_id='" + uid + "'");
        
        Action a = null;
        while (rs3.next()) {
          a = new Action();
          a.setId(rs3.getInt("action_id"));
          a.setName(rs3.getString("name"));
          salesman.addAction(a);
        }
        
        rs3.close();
        stmt3.close();
        
      }
      rs.close();
      stmt.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    
    return salesman;
  }
  
  
  public Sistema getSistema() {
    java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
    java.sql.Statement stmt;
    Sistema sistema = null;
    try {
      stmt = cnn.createStatement();
      java.sql.ResultSet rs = stmt.executeQuery("select * from system ");
      while (rs.next()) {
        sistema= new Sistema();
        sistema.setEstado(rs.getString("estado"));
        sistema.setMotivo(rs.getString("motivo"));

      }
      rs.close();
      stmt.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    
    return sistema;
  }
  
  
  
  public boolean insertOrUpdateSalesman(Object object, String operation) {
    boolean result = false;
    java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
    PreparedStatement stmt;
    
    /* ASObject es el tipo de objeto que Flex nos envia, en realidad es un Map */
    ASObject asobject = (ASObject) object;
    Salesman salesman = new Salesman();
    try {
      /* BeanUtils agarra el objeto salesman y le llena sus valores con el Map Asobject */
      BeanUtils.populate(salesman, asobject);
      
      /* En vista de que el metodo populate no convierte la coleccion llamada subordinates, lo hacemos a pie */
      List subordinates = salesman.getSubordinates();
      for (int i = 0; i < subordinates.size(); i++) {
        Salesman s = new Salesman();
        BeanUtils.populate(s, (ASObject) subordinates.get(i));
        subordinates.set(i, s);
      }
      /* En vista de que el metodo populate no convierte la coleccion llamada grantedActions, lo hacemos a pie */
      List grantedActions = salesman.getGrantedActions();
      for (int i = 0; i < grantedActions.size(); i++) {
        Action a = new Action();
        BeanUtils.populate(a, (ASObject) grantedActions.get(i));
        grantedActions.set(i, a);
      }
      
      try {
        if (operation.equals("update")) {
          stmt = cnn.prepareStatement(SQL_UPDATE_1);
          stmt.setString(10, salesman.getId().trim().toUpperCase());
          stmt.setString(1, salesman.getName().trim());
          stmt.setString(2, salesman.getRegion().trim().toUpperCase());
          stmt.setLong(3, salesman.getTopSerial());
          stmt.setLong(4, salesman.getBottomSerial());
          stmt.setString(5, salesman.getPassword());
          stmt.setLong(6, salesman.getLastSerial());
          stmt.setByte(7, salesman.getMovil());
          stmt.setString(8, salesman.getMail());
          stmt.setString(9, salesman.getMailCoordinador());
        } else {
          stmt = cnn.prepareStatement(SQL_INSERT_2);
          stmt.setString(1, salesman.getId().trim().toUpperCase());
          stmt.setString(2, salesman.getName().trim());
          stmt.setString(3, salesman.getRegion().trim().toUpperCase());
          stmt.setLong(4, salesman.getTopSerial());
          stmt.setLong(5, salesman.getBottomSerial());
          stmt.setString(6, salesman.getPassword());
          stmt.setLong(7, salesman.getLastSerial());
          stmt.setByte(8, salesman.getMovil());
          stmt.setString(9, salesman.getMail());
          stmt.setString(10, salesman.getMailCoordinador());
        }
        
        long ar = stmt.executeUpdate();
        if (ar == 1) {
          
          PreparedStatement stmt2 = cnn.prepareStatement(SQL_DELETE_1);
          stmt2.setString(1, salesman.getId().trim().toUpperCase());
          long ar2 = stmt2.executeUpdate();
          
          for (int i = 0; i < salesman.getSubordinates().size(); i++) {
            PreparedStatement stmt3 = cnn.prepareStatement(SQL_INSERT_1);
            stmt3.setString(1, salesman.getId().trim().toUpperCase());
            stmt3.setString(2, ((Salesman) salesman.getSubordinates().get(i)).getId().trim().toUpperCase());
            long ar3 = stmt3.executeUpdate();
            stmt3.close();
          }
          stmt2.close();
          
          PreparedStatement stmt4 = cnn.prepareStatement(SQL_DELETE_2);
          stmt4.setString(1, salesman.getId().trim().toUpperCase());
          long ar4 = stmt4.executeUpdate();
          
          for (int i = 0; i < salesman.getGrantedActions().size(); i++) {
            PreparedStatement stmt5 = cnn.prepareStatement(SQL_INSERT_3);
            stmt5.setString(1, salesman.getId().trim().toUpperCase());
            stmt5.setInt(2, ((Action) salesman.getGrantedActions().get(i)).getId());
            long ar5 = stmt5.executeUpdate();
            stmt5.close();
          }
          stmt4.close();
          
          result = true;
          
          stmt2.close();
        }
        stmt.close();
      } catch (SQLException ex) {
        ex.printStackTrace();
      }
    } catch (IllegalAccessException ex) {
      ex.printStackTrace();
    } catch (InvocationTargetException ex) {
      ex.printStackTrace();
    }
    return result;
  }
  
  
    public boolean actualizarSistema(Object object) {
    boolean result = false;
    java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
    PreparedStatement stmt;
    
    /* ASObject es el tipo de objeto que Flex nos envia, en realidad es un Map */
    ASObject asobject = (ASObject) object;
    Sistema sistema = new Sistema();
    try {
      /* BeanUtils agarra el objeto salesman y le llena sus valores con el Map Asobject */
      BeanUtils.populate(sistema, asobject);
      
  
      
      try {
        
          stmt = cnn.prepareStatement(SQL_UPDATE_2);
          stmt.setString(1, sistema.getEstado());
          stmt.setString(2, sistema.getMotivo().trim());
          stmt.setString(3, sistema.getUsuario());
          stmt.setString(4, sistema.getFecha());
          stmt.setString(5, DateHelper.formatHhMmSsTime(new java.util.Date()));
          
          
        
        long ar = stmt.executeUpdate();
       
        stmt.close();
     
     
       
      } catch (SQLException ex) {
        ex.printStackTrace();
      }
    } catch (IllegalAccessException ex) {
      ex.printStackTrace();
    } catch (InvocationTargetException ex) {
      ex.printStackTrace();
    }
    return result;
  }
  
  
  
}
